import re
import time
from flask import request
from flask_restful import abort
from flask_login import current_user, login_user
from funcy import project
from sqlalchemy.orm.exc import NoResultFound
from sqlalchemy.exc import IntegrityError
from funcy import partial

from redash import models, limiter
from redash.permissions import (
    require_default,
)
from redash.handlers.base import (
    BaseResource,
    require_fields,
    get_object_or_404,
    paginate,
    order_results as _order_results,
)

from redash.authentication.account import (
    invite_link_for_user,
    send_invite_email,
    send_password_reset_email,
    send_verify_email,
)
from redash.settings import parse_boolean
from redash import settings

class PaymentActivityNewResource(BaseResource):
    def get(self):
        result = models.PaymentActivity.get_id()
        return result[0]

    @require_default
    def post(self):
        req = request.get_json(force=True)
        require_fields(req, ("paymentid","message"))

        paymentactivity = models.PaymentActivity(
        paymentid = req["paymentid"],
        projectid = req["projectid"],
        projectdetailid = req["projectdetailid"],
        employeeid = req["employeeid"],
        code = req["code"],
        name = req["name"],
        contractcode = req["contractcode"],
        amount = req["amount"],
        othertype = req["othertype"],
        otheramount = req["otheramount"],
        signingtime = req["signingtime"],
        expirationtime = req["expirationtime"],
        maintainer = req["maintainer"],
        signatory = req["signatory"],
        recipient = req["recipient"],
        message =req["message"],
        )
        try:
            models.db.session.add(paymentactivity)
            models.db.session.commit()
        except IntegrityError as e:
            if "code" in str(e):
                abort(400, message="编号已存在.")
            abort(500)


class PaymentActivityResource(BaseResource):
    def get(self, paymentactivity_id):
        result = models.PaymentActivity.get_by_id(paymentactivity_id)
        return result.to_dict()

    @require_default
    def post(self, paymentactivity_id):
        req = request.get_json(force=True)
        require_fields(req, ("paymentid","message"))
            
        paymentactivity = models.PaymentActivity.get_by_id(paymentactivity_id)
        params = dict(projectid = req["projectid"],projectdetailid = req["projectdetailid"],employeeid = req["employeeid"],
        code = req["code"],name = req["name"],contractcode = req["contractcode"],amount = req["amount"],
        othertype = req["othertype"],otheramount = req["otheramount"],signingtime = req["signingtime"],
        expirationtime = req["expirationtime"],maintainer = req["maintainer"],signatory = req["signatory"],
        recipient = req["recipient"],message =req["message"])

        try:
            self.update_model(paymentactivity, params)
            models.db.session.commit()
        except IntegrityError as e:
            if "code" in str(e):
                abort(400, message="编号已存在.")
            abort(500)

class PaymentActivityDeleteResource(BaseResource):
    @require_default
    def post(self, paymentactivity_id):
        paymentactivity =  models.PaymentActivity.get_by_id(paymentactivity_id)
        models.db.session.delete(paymentactivity)
        models.db.session.commit()
    
class PaymentActivityListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        page = req["page"]
        page_size = req["pagesize"]
        paymentid = req["paymentid"] if "paymentid" in req else ""
        message = req["message"] if "message" in req else ""
        orderfield = req["orderfield"] if "orderfield" in req else "id"
        order = req["order"] if "order" in req else 1

        
        if paymentid !="" :
            strwhere ="t1.paymentid=" +str(paymentid)
        if message !="" :
            strwhere = strwhere + " and t1.message like '%"+message+"%'"

        strgetfield =""" t1.id,t1.paymentid,t1.projectid,t1.projectdetailid,t1.employeeid,
            t1.code,t1.name,t1.contractcode,t1.amount,t1.othertype,t1.otheramount,t1.signingtime,
            t1.expirationtime,t1.maintainer,t1.signatory,t1.recipient,t1.message,
            t2.code as projectcode,
            COALESCE(t3.name,'') as maintainername,COALESCE(t4.name,'') as signatoryname,COALESCE(t5.name,'')  as recipientname,
            t2.customerid """

        tblname = """public.paymentactivity t1 inner join public.project t2 on t1.projectid=t2.id
            left join public.users t3 on t1.maintainer=t3.id
            left join public.users t4 on t1.signatory=t4.id
            left join public.users t5 on t1.recipient=t5.id"""

        result = models.Enums.get_pagequery(tblname,strgetfield,orderfield,page_size,page,order,strwhere)
        return result[0]
class PaymentActivityunListResource(BaseResource):
    def post(self):
        req = request.get_json(force=True)
        page = req["page"]
        page_size = req["pagesize"]
        projectdetailid = req["projectdetailid"] if "projectdetailid" in req else "0"
        orderfield = req["orderfield"] if "orderfield" in req else "id"
        paytype = str(req["paytype"]) if "paytype" in req else "0"
        order = req["order"] if "order" in req else 1

        strwhere = " t1.id in("+projectdetailid+")"

        strgetfield =""" 0 as id,0 paymentid,t1.projectid,t1.id as projectdetailid,t1.employeeid,"""
        if paytype=="0":
            strgetfield= strgetfield+"""t1.code,t1.name,t1.contractcode,t1.signamount-COALESCE(t7.amount,0) as amount,"""
        else:
            strgetfield= strgetfield+"""t2.code,t2.name,t1.contractcode,t1.amount-COALESCE(t7.amount,0) as amount,"""
        strgetfield= strgetfield+"""'' as othertype,0 as otheramount,t2.signingtime,
            t2.expirationtime,t6.maintainer,t6.signatory,t6.userid as recipient,t1.message,
            t2.code as projectcode,
            COALESCE(t3.name,'') as maintainername,COALESCE(t4.name,'') as signatoryname,COALESCE(t5.name,'')  as recipientname,
            t2.customerid """

        tblname = """public.projectdetail t1 inner join public.project t2 on t1.projectid=t2.id
            inner join public.employee t6 on t1.employeeid=t6.id
            left join public.users t3 on t6.maintainer=t3.id
            left join public.users t4 on t6.signatory=t4.id
            left join public.users t5 on t6.userid=t5.id
            left join (select t2.projectid,t2.projectdetailid,sum(t2.amount) as amount
            from public.payment t1 inner join public.paymentactivity t2 on t1.id=t2.paymentid
            where t1.type="""+paytype+""" group by t2.projectid,t2.projectdetailid) t7 on t2.id=t7.projectid and t1.id=t7.projectdetailid"""

        result = models.Enums.get_pagequery(tblname,strgetfield,orderfield,page_size,page,order,strwhere)
        return result[0]

class PaymentActivityinsert_update:
    def insert_update(self, paylist,payment_id):
        try:
            code="''";
            strsql=""
            for item in paylist:
                # strsqlcheck="""select t1.amount-COALESCE(t2.amount,0) as amount
                #     from public.projectdetail t1 left join (select sum(t1.amount) as amount,t1.projectdetailid,t1.projectid
                #     From paymentactivity t1 inner join payment t2 on t1.paymentid=t2.id 
                #     where t2.type=0 and t2.id <>'"""+str(payment_id)+"""'  
                #     and t1.projectdetailid ='"""+str(item["projectdetailid"])+"""' and t1.projectid="""+str(item["projectid"])+"""
                #     group by t1.projectdetailid,t1.projectid)t2 on t1.id=t2.projectdetailid and t1.projectid=t2.projectid"""
                # amount=0
                # idcardcheck = models.Employee.check_employee_field(employee_id,"idcard",idcard)
                # if idcardcheck is not None:
                #     abort(400, message="身份证号已存在:"+idcardcheck[0]+",不能重复.")

                if(item["id"]==0) :
                    strsql=strsql+""" insert into public.PaymentActivity(paymentid,projectid,projectdetailid,employeeid,
                    code,name,contractcode,amount,othertype,otheramount,signingtime,expirationtime,maintainer,signatory,
                    recipient,message) """
                    strsql=strsql+"""select """+str(payment_id)+""","""+str(item["projectid"])+""","""+str(item["projectdetailid"])+""","""+str(item["employeeid"])+""",
                    '"""+item["code"]+"""','"""+item["name"]+"""','"""+item["contractcode"]+"""','"""+str(item["amount"])+"""','"""+item["othertype"]+"""',
                    '"""+str(item["otheramount"])+"""',"""
                    if item["signingtime"]==None :
                        strsql=strsql +"""null,"""
                    else :
                        strsql=strsql +"""'"""+item["signingtime"]+"""',"""

                    if item["expirationtime"]==None :
                        strsql=strsql +"""null,"""
                    else :
                        strsql=strsql +"""'"""+item["expirationtime"]+"""',"""

                    strsql=strsql +"""'"""+str(item["maintainer"])+"""','"""+str(item["signatory"])+"""',
                     '"""+str(item["recipient"])+"""','"""+item["message"]+"""'; """

                    # paymentactivity = models.PaymentActivity(
                    #     paymentid = payment_id,
                    #     projectid = item["projectid"],
                    #     projectdetailid = item["projectdetailid"],
                    #     employeeid = item["employeeid"],
                    #     code = item["code"],
                    #     name = item["name"],
                    #     contractcode = item["contractcode"],
                    #     amount = item["amount"],
                    #     othertype = item["othertype"],
                    #     otheramount = item["otheramount"],
                    #     signingtime = item["signingtime"],
                    #     expirationtime = item["expirationtime"],
                    #     maintainer = item["maintainer"],
                    #     signatory = item["signatory"],
                    #     recipient = item["recipient"],
                    #     message =item["message"],
                    #     )
                    code=code+",'"+item["code"]+"'"
                    # models.db.session.add(paymentactivity)
                else :
                    strsql=strsql+""" update public.PaymentActivity set projectid="""+str(item["projectid"])+""",
                    projectdetailid="""+str(item["projectdetailid"])+""",employeeid="""+str(item["employeeid"])+""",
                    code='"""+item["code"]+"""',name='"""+item["name"]+"""',contractcode='"""+item["contractcode"]+"""',
                    amount='"""+str(item["amount"])+"""',othertype='"""+item["othertype"]+"""',otheramount='"""+str(item["otheramount"])+"""',"""
                    if item["signingtime"]==None :
                        strsql=strsql+"""signingtime=null,"""
                    else :
                        strsql=strsql+"""signingtime='"""+item["signingtime"]+"""',"""


                    if item["expirationtime"]==None :
                        strsql=strsql+"""expirationtime=null,"""

                    else :
                        strsql=strsql+"""expirationtime='"""+item["expirationtime"]+"""',"""

                    strsql=strsql+"""maintainer='"""+str(item["maintainer"])+"""',signatory='"""+str(item["signatory"])+"""',
                    recipient='"""+str(item["recipient"])+"""',message='"""+item["message"]+"""' ; """

                    # paymentactivity = models.PaymentActivity.get_by_id(item["id"])
                    # params = dict(projectid = item["projectid"],projectdetailid = item["projectdetailid"],employeeid = item["employeeid"],
                    #     code = item["code"],name = item["name"],contractcode = item["contractcode"],amount = item["amount"],
                    #     othertype = item["othertype"],otheramount = item["otheramount"],signingtime = item["signingtime"],
                    #     expirationtime = item["expirationtime"],maintainer = item["maintainer"],signatory = item["signatory"],
                    #     recipient = item["recipient"],message =item["message"])
                    code=code+",'"+item["code"]+"'"
                    # self.update_model(paymentactivity, params)
            strsql=strsql+"""delete from public.paymentactivity where paymentid = """+str(payment_id)+""" and code not in("""+code+""") ; """
            models.db.session.execute(strsql)
            return "保存成功！"
        except :
            abort(400, message="修改失败.") 